## A.6. DBI Database Handle Objects

This section covers the methods and attributes associated with database handles.

### A.6.1. Database Handle Methods

The following methods are specified for DBI database handles:

do |

    $rc  = $dbh->do($statement)           || die $dbh->errstr;
    $rc  = $dbh->do($statement, \%attr)   || die $dbh->errstr;
    $rv  = $dbh->do($statement, \%attr, @bind_values) || ...

Prepares and executes a single statement. Returns the number of rows affected or `undef` on error. A return value of `-1` means the number of rows is not known or is not available.

This method is typically most useful for non-`SELECT` statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for `SELECT` statements because it does not return a statement handle (so you can't fetch any data).

The default `do` method is logically similar to:

>     sub do {
>         my($dbh, $statement, $attr, @bind_values) = @_;
>         my $sth = $dbh->prepare($statement, $attr) or return undef;
>         $sth->execute(@bind_values) or return undef;
>         my $rows = $sth->rows;
>         ($rows == 0) ? "0E0" : $rows; # always return true if no error
>     }

For example:

>     my $rows_deleted = $dbh->do(q{
>         DELETE FROM table
>         WHERE status = ?
>     }, undef, 'DONE') || die $dbh->errstr;

Using placeholders and `@bind_values` with the `do` method can be useful because it avoids the need to correctly quote any variables in the `$statement`. But if you'll be executing the statement many times, then it's more efficient to `prepare` it once and call `execute` many times instead.

The `q{...}` style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote-like `qq{...}` operator if you want to interpolate variables into the string. See the section on "Quote and Quote-Like Operators" in the perlop manpage for more details.

selectrow_array |

    @row_ary = $dbh->selectrow_array($statement);
    @row_ary = $dbh->selectrow_array($statement, \%attr);
    @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);

This utility method combines `prepare`, `execute`, and `fetchrow_array` into a single call. If called in a list context, it returns the first row of data from the statement. If called in a scalar context, it returns the first field of the first row. The `$statement` parameter can be a previously prepared statement handle, in which case the `prepare` is skipped.

If any method fails, and `RaiseError` is not set, `selectrow_array` will return an empty list.

In a scalar context, `selectrow_array` returns the value of the first field. An `undef` is returned if there are no matching rows or if an error occurred. Since that `undef` can't be distinguished from an `undef` returned because the first field value was NULL, calling `selectrow_array` in a scalar context should be used with caution.

selectall_arrayref |

    $ary_ref = $dbh->selectall_arrayref($statement);
    $ary_ref = $dbh->selectall_arrayref($statement, \%attr);
    $ary_ref = $dbh->selectall_arrayref($statement, \%attr,
    @bind_values);

This utility method combines `prepare`, `execute`, and `fetchall_arrayref` into a single call. It returns a reference to an array containing a reference to an array for each row of data fetched.

The `$statement` parameter can be a previously prepared statement handle, in which case the `prepare` is skipped. This is recommended if the statement is going to be executed many times.

If any method except `fetch` fails, and `RaiseError` is not set, `selectall_arrayref` will return `undef`. If `fetch` fails, and `RaiseError` is not set, then it will return with whatever data it has fetched thus far.

selectcol_arrayref |

    $ary_ref = $dbh->selectcol_arrayref($statement);
    $ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
    $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);

This utility method combines `prepare`, `execute`, and fetching one column from all the rows, into a single call. It returns a reference to an array containing the values of the first column from each row.

The `$statement` parameter can be a previously prepared statement handle, in which case the `prepare` is skipped. This is recommended if the statement is going to be executed many times.

If any method except `fetch` fails, and `RaiseError` is not set, `selectcol_arrayref` will return `undef`. If `fetch` fails and `RaiseError` is not set, then it will return with whatever data it has fetched thus far.

prepare |

    $sth = $dbh->prepare($statement)          || die $dbh->errstr;
    $sth = $dbh->prepare($statement, \%attr)  || die $dbh->errstr;

Prepares a single statement for later execution by the database engine and returns a reference to a statement handle object.

The returned statement handle can be used to get attributes of the statement and invoke the `execute` method. See "Statement Handle Methods."

Drivers for engines without the concept of preparing a statement will typically just store the statement in the returned handle and process it when `$sth-`>`execute` is called. Such drivers are unlikely to give much useful information about the statement, such as `$sth-`>`{NUM_OF_FIELDS}`, until after `$sth-`>`execute` has been called. Portable applications should take this into account.

In general, DBI drivers do not parse the contents of the statement (other than simply counting any placeholders). The statement is passed directly to the database engine, sometimes known as pass-thru mode. This has advantages and disadvantages. On the plus side, you can access all the functionality of the engine being used. On the downside, you're limited if you're using a simple engine, and you need to take extra care if you're writing applications intended to be portable between engines.

Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement.

Some command-line SQL tools use statement terminators, like a semicolon, to indicate the end of a statement. Such terminators should not normally be used with the DBI.

prepare_cached |

    $sth = $dbh->prepare_cached($statement)
    $sth = $dbh->prepare_cached($statement, \%attr)
    $sth = $dbh->prepare_cached($statement, \%attr, $allow_active)

Like `prepare` except that the statement handle returned will be stored in a hash associated with the `$dbh`. If another call is made to `prepare_cached` with the same `$statement` and `%attr` values, then the corresponding cached `$sth` will be returned without contacting the database server.

This caching can be useful in some applications, but it can also cause problems and should be used with care. A warning will be generated if the cached `$sth` being returned is active (i.e., it is a `SELECT` that may still have data to be fetched). This warning can be suppressed by setting `$allow_active` to true. The cache can be accessed (and cleared) via the `CachedKids` attribute.

Here's an example of one possible use of `prepare_cached`:

>     while ( ($field, $value) = each %search_fields ) {
>         push @sql,   "$field = ?";
>         push @values, $value;
>     }
>     $qualifier = "";
>     $qualifier = "where ".join(" and ", @sql) if @sql;
>     $sth = $dbh->prepare_cached("SELECT * FROM table $qualifier");
>     $sth->execute(@values);

commit |

    $rc  = $dbh->commit     || die $dbh->errstr;

Commits (makes permanent) the most recent series of database changes if the database supports transactions and `AutoCommit` is off.

If `AutoCommit` is on, then calling `commit` will issue a "commit ineffective with AutoCommit" warning.

rollback |

    $rc  = $dbh->rollback   || die $dbh->errstr;

Rolls back (undoes) the most recent series of uncommitted database changes if the database supports transactions and `AutoCommit` is off.

If `AutoCommit` is on, then calling `rollback` will issue a "rollback ineffective with AutoCommit" warning.

disconnect |

    $rc = $dbh->disconnect  || warn $dbh->errstr;

Disconnects the database from the database handle. `disconnect` is typically used only before exiting the program. The handle is of little use after disconnecting.

The transaction behavior of the `disconnect` method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will roll back any outstanding changes. Applications not using `AutoCommit` should explicitly call `commit` or `rollback` before calling `disconnect`.

The database is automatically disconnected by the `DESTROY` method if still connected when there are no longer any references to the handle. The `DESTROY` method for each driver should implicitly call `rollback` to undo any uncommitted changes. This is vital behavior to ensure that incomplete transactions don't get committed simply because Perl calls `DESTROY` on every object before exiting. Also, do not rely on the order of object destruction during "global destruction," as it is undefined.

Generally, if you want your changes to be commited or rolled back when you disconnect, then you should explicitly call `commit` or `rollback` before disconnecting.

If you disconnect from a database while you still have active statement handles, you will get a warning. The statement handles should either be cleared (destroyed) before disconnecting, or the `finish` method should be called on each one.

ping |

    $rc = $dbh->ping;

Attempts to determine, in a reasonably efficient way, if the database server is still running and the connection to it is still working. Individual drivers should implement this function in the most suitable manner for their database engine.

The current default implementation always returns true without actually doing anything. Actually, it returns "`0` `but` `true`", which is true but zero. That way you can tell if the return value is genuine or just the default. Drivers should override this method with one that does the right thing for their type of database.

Few applications would have use for this method. See the specialized `Apache::DBI` module for one example usage.

table_info (NEW ) |

    $sth = $dbh->table_info;

Warning: This method is experimental and may change.

Returns an active statement handle that can be used to fetch information about tables and views that exist in the database.

The handle has at least the following fields in the order show below. Other fields, after these, may also be present.

TABLE_CAT

  Table catalog identifier. This field is NULL (`undef`) if not applicable to the data source, which is usually the case. This field is empty if not applicable to the table.

TABLE_SCHEM

  The name of the schema containing the TABLE_NAME value. This field is NULL (`undef`) if not applicable to data source, and empty if not applicable to the table.

TABLE_NAME

  Name of the table (or view, synonym, etc.).

TABLE_TYPE

  One of the following: "TABLE," "VIEW," "SYSTEM TABLE," "GLOBAL TEMPORARY," "LOCAL TEMPORARY," "ALIAS," "SYNONYM," or a type identifier that is specific to the data source.

REMARKS

  A description of the table. May be NULL (`undef`).

Note that `table_info` might not return records for all tables. Applications can use any valid table regardless of whether it's returned by `table_info`. See also `tables`.

For more detailed information about the fields and their meanings, refer to:

> http://msdn.microsoft.com/library/psdk/dasdk/odch6wqb.htm

If that URL ceases to work, then use the MSDN search facility at:

> http://search.microsoft.com/us/dev/

and search for `SQLTables` `returns` using the exact phrase option. The link you want will probably just be called `SQLTables` and will be part of the Data Access SDK.

tables (NEW ) |

    @names = $dbh->tables;

Warning: This method is experimental and may change.

Returns a list of table and view names, possibly including a schema prefix. This list should include all tables that can be used in a `SELECT` statement without further qualification.

Note that `table_info` might not return records for all tables. Applications can use any valid table regardless of whether it's returned by tables. See also `table_info`.

type_info_all (NEW ) |

    $type_info_all = $dbh->type_info_all;

Warning: This method is experimental and may change.

Returns a reference to an array that holds information about each datatype variant supported by the database and driver. The array and its contents should be treated as read-only.

The first item is a reference to a hash of `Name =`> `Index` pairs. The following items are references to arrays, one per supported datatype variant. The leading hash defines the names and order of the fields within the following list of arrays. For example:

>     $type_info_all = [
>       {   TYPE_NAME         => 0,
>           DATA_TYPE         => 1,
>           COLUMN_SIZE       => 2,     # was PRECISION originally
>           LITERAL_PREFIX    => 3,
>           LITERAL_SUFFIX    => 4,
>           CREATE_PARAMS     => 5,
>           NULLABLE          => 6,
>           CASE_SENSITIVE    => 7,
>           SEARCHABLE        => 8,
>           UNSIGNED_ATTRIBUTE=> 9,
>           FIXED_PREC_SCALE  => 10,    # was MONEY originally
>           AUTO_UNIQUE_VALUE => 11,    # was AUTO_INCREMENT originally
>           LOCAL_TYPE_NAME   => 12,
>           MINIMUM_SCALE     => 13,
>           MAXIMUM_SCALE     => 14,
>           NUM_PREC_RADIX    => 15,
>       },
>       [ 'VARCHAR', SQL_VARCHAR,
>           undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef
>       ],
>       [ 'INTEGER', SQL_INTEGER,
>           undef,  "", "", undef,0, 0,1,0,0,0,undef,0,  0, 10
>       ],
>     ];

Note that more than one row may have the same value in the `DATA_TYPE` field if there are different ways to spell the type name and/or there are variants of the type with different attributes (e.g., with and without `AUTO_UNIQUE_VALUE` set, with and without `UNSIGNED_ATTRIBUTE`, etc.).

The rows are ordered by `DATA_TYPE` first and then by how closely each type maps to the corresponding ODBC SQL datatype, closest first.

The meaning of the fields is described in the documentation for the `type_info` method. The index values shown above (e.g., `NULLABLE =`> `6`) are for illustration only. Drivers may define the fields with a different order.

This method is not normally used directly. The `type_info` method provides a more useful interface to the data.

type_info (NEW ) |

    @type_info = $dbh->type_info($data_type);

Warning: This method is experimental and may change.

Returns a list of hash references holding information about one or more variants of `$data_type`. The list is ordered by `DATA_TYPE` first and then by how closely each type maps to the corresponding ODBC SQL datatype, closest first. If called in a scalar context then only the first (best) element is returned.

If `$data_type` is undefined or `SQL_ALL_TYPES`, then the list will contain hashes for all datatype variants supported by the database and driver.

If `$data_type` is an array reference, then `type_info` returns the information for the first type in the array that has any matches.

The keys of the hash follow the same letter case conventions as the rest of the DBI (see "Naming Conventions and Name Space"). The following items should exist:

TYPE_NAME (string)

  Datatype name for use in `CREATE` `TABLE` statements, etc.

DATA_TYPE (integer)

  SQL datatype number.

COLUMN_SIZE (integer)

  For numeric types, this is either the total number of digits (if the `NUM_PREC_RADIX` value is `10`) or the total number of bits allowed in the column (if `NUM_PREC_RADIX` is `2`).

  For string types, this is the maximum size of the string in bytes.

  For date and interval types, this is the maximum number of characters needed to display the value.

LITERAL_PREFIX (string)

  Characters used to prefix a literal. A typical prefix is "`'`" for characters, or possibly "`0x`" for binary values passed as hexadecimal. NULL (`undef`) is returned for datatypes for which this is not applicable.

LITERAL_SUFFIX (string)

  Characters used to suffix a literal. Typically "`'`" for characters. NULL (`undef`) is returned for datatypes where this is not applicable.

CREATE_PARAMS (string)

  Parameters for a datatype definition. For example, `CREATE_PARAMS` for a `DECIMAL` would be "`precision,scale`" if the `DECIMAL` type should be declared as `DECIMAL(`precision`,`scale`)` where precision and scale are integer values. For a `VARCHAR` it would be "`max` `length`". NULL (`undef`) is returned for datatypes for which this is not applicable.

NULLABLE (integer)

  Indicates whether the datatype accepts a NULL value: `0` = no, `1` = yes, `2` = unknown.

CASE_SENSITIVE ( boolean)

  Indicates whether the datatype is case-sensitive in collations and comparisons.

SEARCHABLE (integer)

  Indicates how the datatype can be used in a `WHERE` clause, as follows:

  0

    Cannot be used in a `WHERE` clause

  1

    Only with a `LIKE` predicate

  2

    All comparison operators except `LIKE`

  3

    Can be used in a `WHERE` clause with any comparison operator

UNSIGNED_ATTRIBUTE ( boolean)

  Indicates whether the datatype is unsigned. NULL (`undef`) is returned for datatypes for which this is not applicable.

FIXED_PREC_SCALE ( boolean)

  Indicates whether the datatype always has the same precision and scale (such as a money type). NULL (`undef`) is returned for datatypes for which this is not applicable.

AUTO_UNIQUE_VALUE ( boolean)

  Indicates whether a column of this datatype is automatically set to a unique value whenever a new row is inserted. NULL (`undef`) is returned for datatypes for which this is not applicable.

LOCAL_TYPE_NAME (string)

  Localized version of the `TYPE_NAME` for use in dialog with users. NULL (`undef`) is returned if a localized name is not available (in which case `TYPE_NAME` should be used).

MINIMUM_SCALE (integer)

  The minimum scale of the datatype. If a datatype has a fixed scale, then `MAXIMUM_SCALE` holds the same value. NULL (`undef`) is returned for datatypes for which this is not applicable.

MAXIMUM_SCALE (integer)

  The maximum scale of the datatype. If a datatype has a fixed scale, then `MINIMUM_SCALE` holds the same value. NULL (`undef`) is returned for datatypes for which this is not applicable.

SQL_DATA_TYPE (integer)

  This column is the same as the `DATA_TYPE` column, except for interval and datetime datatypes. For interval and datetime datatypes, the `SQL_DATA_TYPE` field will return `SQL_INTERVAL` or `SQL_DATETIME`, and the `SQL_DATETIME_SUB` field below will return the subcode for the specific interval or datetime datatype. If this field is NULL, then the driver does not support or report on interval or date subtypes.

SQL_DATETIME_SUB (integer)

  For interval or datetime datatypes, where the `SQL_DATA_TYPE` field above is `SQL_INTERVAL` or `SQL_DATETIME`, this field will hold the subcode for the specific interval or datetime datatype. Otherwise it will be NULL (`undef`).

NUM_PREC_RADIX (integer)

  The radix value of the datatype. For approximate numeric types, `NUM_PREC_RADIX` contains the value `2` and `COLUMN_SIZE` holds the number of bits. For exact numeric types, `NUM_PREC_RADIX` contains the value `10` and `COLUMN_SIZE` holds the number of decimal digits. NULL (`undef`) is returned either for datatypes for which this is not applicable or if the driver cannot report this information.

INTERVAL_PRECISION (integer)

  The interval leading precision for interval types. NULL is returned either for datatypes for which this is not applicable or if the driver cannot report this information.

Since DBI and ODBC drivers vary in how they map their types into the ISO standard types, you may need to search for more than one type. Here's an example looking for a usable type to store a date:

>     $my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] );

Similarly, to more reliably find a type to store small integers, you could use a list starting with `SQL_SMALLINT`, `SQL_INTEGER`, `SQL_DECIMAL`, etc.

For more detailed information about these fields and their meanings, refer to:

> http://msdn.microsoft.com/library/psdk/dasdk/odch6yy7.htm

If that URL ceases to work, then use the MSDN search facility at:

> http://search.microsoft.com/us/dev/

and search the MSDN library for `SQLGetTypeInfo` `returns` using the exact phrase option. The link you want will probably just be called `SQLGetTypeInfo` (there may be more than one).

The individual datatypes are currently described here:

> http://msdn.microsoft.com/library/psdk/dasdk/odap8fcj.htm

If that URL ceases to work, or to get more general information, use the MSDN search facility as described above, and search for `SQL` `Data` `Types`.

quote |

    $sql = $dbh->quote($value);
    $sql = $dbh->quote($value, $data_type);

Quotes a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

>     $sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
>                   $dbh->quote("Don't");

For most database types, `quote` would return `'Don''t'` (including the outer quotation marks).

An undefined `$value` value will be returned as the string `NULL` (without quotation marks) to match how NULLs are represented in SQL.

If `$data_type` is supplied, it is used to try to determine the required quoting behavior by using the information returned by `type_info`. As a special case, the standard numeric types are optimized to return `$value` without calling `type_info`.

`quote` will probably not be able to deal with all possible input (such as binary data or data containing newlines), and is not related in any way with escaping or quoting shell meta-characters. There is no need to quote values being used with placeholders and bind values.

### A.6.2. Database Handle Attributes

This section describes attributes specific to database handles.

Changes to these database handle attributes do not affect any other existing or future database handles.

Attempting to set or get the value of an unknown attribute is fatal, except for private driver-specific attributes (which all have names starting with a lowercase letter).

For example:

>     $h->{AutoCommit} = ...;       # set/write
>     ... = $h->{AutoCommit};       # get/read

AutoCommit ( boolean) |

If true, then database changes cannot be rolled back (undone). If false, then database changes automatically occur within a "transaction," which must either be committed or rolled back using the `commit` or `rollback` methods.

Drivers should always default to `AutoCommit` mode (an unfortunate choice largely forced on the DBI by ODBC and JDBC conventions.)

Attempting to set `AutoCommit` to an unsupported value is a fatal error. This is an important feature of the DBI. Applications that need full transaction behavior can set `$dbh-`>`{AutoCommit}` `=` `0` (or set `AutoCommit` to `0` via `connect`) without having to check that the value was assigned successfully.

For the purposes of this description, we can divide databases into three categories:

> Databases that don't support transactions at all.

> Databases in which a transaction is always active.

> Databases in which a transaction must be explicitly started (`'BEGIN WORK'`).

### A.6.2.1. Databases that don't support transactions at all

For these databases, attempting to turn `AutoCommit` off is a fatal error. `commit` and `rollback` both issue warnings about being ineffective while `AutoCommit` is in effect.

### A.6.2.2. Databases in which a transaction is always active

These are typically mainstream commercial relational databases with "ANSI standard" transaction behavior. If `AutoCommit` is off, then changes to the database won't have any lasting effect unless `commit` is called (but see also `disconnect`). If `rollback` is called, then any changes since the last `commit` are undone.

If `AutoCommit` is on, then the effect is the same as if the DBI called `commit` automatically after every successful database operation. In other words, calling `commit` or `rollback` explicitly while `AutoCommit` is on would be ineffective because the changes would have already been commited.

Changing `AutoCommit` from off to on should issue a `commit` in most drivers.

Changing `AutoCommit` from on to off should have no immediate effect.

For databases that don't support a specific autocommit mode, the driver has to commit each statement automatically using an explicit `COMMIT` after it completes successfully (and roll it back using an explicit `rollback` if it fails). The error information reported to the application will correspond to the statement that was executed, unless it succeeded and the `commit` or `rollback` failed.

### A.6.2.3. Databases in which a transaction must be explicitly started

For these databases, the intention is to have them act like databases in which a transaction is always active (as described earlier).

To do this, the DBI driver will automatically begin a transaction when `AutoCommit` is turned off (from the default "on" state) and will automatically begin another transaction after a `commit` or `rollback`. In this way, the application does not have to treat these databases as a special case.

See `disconnect` for other important notes about transactions.

Driver ( handle) |

Holds the handle of the parent driver. The only recommended use for this attribute is to find the name of the driver using:

>     $dbh->{Driver}->{Name}

Name (string) |

Holds the "name" of the database. Usually (and recommended to be) the same as the "`dbi:DriverName:...`" string used to connect to the database, but with the leading `dbi:DriverName:` removed.

RowCacheSize (integer) (NEW ) |

A hint to the driver indicating the size of the local row cache that the application would like the driver to use for future `SELECT` statements. If a row cache is not implemented, then setting `RowCacheSize` is ignored and getting the value returns `undef`.

Some `RowCacheSize` values have special meaning, as follows:

0

  Automatically determine a reasonable cache size for each `SELECT`.

1

  Disable the local row cache.

>1

  Cache this many rows.

<0

  Cache as many rows that will fit into this much memory for each `SELECT`.

Note that large cache sizes may require a very large amount of memory (cached rows × maximum size of row). Also, a large cache will cause a longer delay not only for the first fetch, but also whenever the cache needs refilling.

See also the `RowsInCache` statement handle attribute.

Copyright © 2001 O'Reilly & Associates. All rights reserved.